# coding:utf-8

import os
import set_env_path     # 请勿删除

import time
import json

import requests
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine, text
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

PHONE_FORMAT = r'1(3|4|5|7|8)\d{9}'

API = r'http://cx.shouji.360.cn/phonearea.php?number={phone}'

# 广东的area_id直接写在这里, 不查询了
GUANGDONG_ID = 440000
WAR_ZONE_ID = 2


def get_timestamp():
    return int(time.time())


class Area(Base):
    """
    省，市，县/区
    """
    __tablename__ = 'area'

    id = Column(Integer, primary_key=True)
    created_at = Column(Integer, nullable=False, default=get_timestamp)
    updated_at = Column(Integer, nullable=False, default=get_timestamp, onupdate=get_timestamp)
    deleted_at = Column(Integer, nullable=False, default=0)

    type = Column(Integer, nullable=False, default=0)
    name = Column(String(32, 'utf8mb4_unicode_ci'), nullable=False, default='',)
    pid = Column(Integer, nullable=False, default=0)
    war_zone_id = Column(Integer)

engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
Session = sessionmaker(bind=engine)
session = Session()


def sync_all_user_local():
    """
    created by chenhj at 2017/8/4
    同步用户的phone_area_id字段和war_zone_id
    """
    select_all_user_sql = """
        select id, mobile from user where phone_area_id is null and deleted_at=0 and 
        is_virt is False and mobile like '1%' and LENGTH(mobile) = 11
    """

    update_user_war_zone_sql = text("""
        update user set phone_area_id=:phone_area_id, war_zone_id=:war_zone_id
        where id=:id
    """)

    result = session.execute(select_all_user_sql).fetchall()
    cmt_cnt = 0
    with open('phone_area.txt', 'rb') as f:
        phone_area_dic = json.loads(f.read())
    for each in result:
        print(each.id, each.mobile)

        # 填充扩展字段prov
        phone = each.mobile
        phone_prefix = phone[:7]
        if phone_prefix in phone_area_dic:
            prov = phone_area_dic[phone_prefix]
            if prov:
                area = session.query(Area).filter(
                    Area.name.like('{}%'.format(prov)),
                    Area.type == 1
                ).first()
                if area:
                    phone_area_id = area.id
                    war_zone_id = area.war_zone_id
                    print(phone_area_id, war_zone_id)
                    session.execute(
                        update_user_war_zone_sql,
                        {
                            'phone_area_id': phone_area_id,
                            'war_zone_id': war_zone_id,
                            'id': each.id
                        }
                    )
                    cmt_cnt += 1
                    if cmt_cnt >= 50:
                        print('session commit...')
                        session.commit()
                        cmt_cnt = 0

    if cmt_cnt:
        session.commit()
    session.close()


def sync_all_user_360():
    """
    created by chenhj at 2017/8/4
    同步用户的phone_area_id字段和war_zone_id
    """
    select_all_user_sql = """
        select id, mobile from user where phone_area_id is null and deleted_at=0 and 
        is_virt is False and mobile like '1%' and LENGTH(mobile) = 11
    """

    update_user_war_zone_sql = text("""
        update user set phone_area_id=:phone_area_id, war_zone_id=:war_zone_id
        where id=:id
    """)

    result = session.execute(select_all_user_sql).fetchall()
    cmt_cnt = 0
    for each in result:
        print(each.id, each.mobile)

        # 填充扩展字段prov
        phone = each.mobile
        try:
            phone_info = json.loads(requests.get(API.format(phone=phone)).content)
        except:
            print('exc...')
            continue
        if phone_info['code'] == 0:
            prov = phone_info['data']['province']
            area = None
            if prov:
                area = session.query(Area).filter(
                    Area.name.like('{}%'.format(prov)),
                    Area.type == 1
                ).first()
            phone_area_id = area.id if area else GUANGDONG_ID
            war_zone_id = area.war_zone_id if area else WAR_ZONE_ID
        else:
            phone_area_id = GUANGDONG_ID
            war_zone_id = WAR_ZONE_ID
        print(phone_area_id, war_zone_id)
        session.execute(
            update_user_war_zone_sql,
            {
                'phone_area_id': phone_area_id,
                'war_zone_id': war_zone_id,
                'id': each.id
            }
        )
        cmt_cnt += 1
        if cmt_cnt >= 50:
            print('session commit...')
            session.commit()
            cmt_cnt = 0

    if cmt_cnt:
        session.commit()
    session.close()


if __name__ == '__main__':
    sync_all_user_local()
    sync_all_user_360()
